﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class GuiaRepository : IRepository<Guia>
    {
        private IDbCommand Command { get; set; }

        public GuiaRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public Guia GetById(string numeroGuia, int loteGuia)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, local, idLoteGuia,dataEmissao,glosa,reapresentada,numeroGuiaOperadora FROM tbGuia WHERE numeroGuia='" + 
                    numeroGuia + "' and idLoteGuia=" + loteGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Guia guia = new Guia();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.LoteGuia = int.Parse(reader["idLoteGuia"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());
                    guia.Glosa = Convert.ToBoolean(reader["glosa"].ToString());
                    guia.Reapresentada = Convert.ToBoolean(reader["reapresentada"].ToString());
                    guia.NumeroGuiaOperadora = reader["numeroGuiaOperadora"].ToString();

                    return guia;
                }
                else
                {
                    throw new Exception("Guia não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<Guia> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, local,idLoteGuia,dataEmissao,glosa,reapresentada,numeroGuiaOperadora FROM tbGuia";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<Guia> list = new List<Guia>();
                while (reader.Read())
                {
                    Guia guia = new Guia();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.LoteGuia = int.Parse(reader["idLoteGuia"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());
                    guia.Glosa = Convert.ToBoolean(reader["glosa"].ToString());
                    guia.Reapresentada = Convert.ToBoolean(reader["reapresentada"].ToString());
                    guia.NumeroGuiaOperadora = reader["numeroGuiaOperadora"].ToString();

                    list.Add(guia);
                }
                return list.AsQueryable<Guia>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(Guia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("INSERT INTO tbGuia (numeroGuia, local, idLoteGuia,dataEmissao,glosa,reapresentada,numeroGuiaOperadora) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",entity.NumeroGuia,entity.Local,entity.LoteGuia,entity.DataEmissao,entity.Glosa,entity.Reapresentada,entity.NumeroGuiaOperadora);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();

                return -1;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(Guia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbGuia WHERE numeroGuia='" + entity.NumeroGuia + "' and idLoteGuia=" + entity.LoteGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(Guia entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("UPDATE tbGuia SET local='{1}',dataEmissao='{3}',glosa='{4}',reapresentada='{5}',numeroGuiaOperadora='{6}' WHERE numeroGuia='{0}' and idLoteGuia={2}", 
                    entity.NumeroGuia, entity.Local, entity.LoteGuia, entity.DataEmissao, entity.Glosa, entity.Reapresentada, entity.NumeroGuiaOperadora);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
